<?php

use think\migration\Migrator;
use think\migration\db\Column;

class LawyerSystemReform extends Migrator
{
    public function up()
    {
        // 1. 扩展oa_lawyer表
        $table = $this->table('oa_lawyer');
        $table->addColumn('admin_id', 'integer', [
            'limit' => 11,
            'default' => 0,
            'comment' => '关联员工ID'
        ])
        ->addColumn('can_login', 'boolean', [
            'default' => 0,
            'comment' => '是否可登录系统:0不可登录,1可登录'
        ])
        ->addColumn('sync_status', 'boolean', [
            'default' => 0,
            'comment' => '同步状态:0未同步,1已同步'
        ])
        ->addIndex(['admin_id'])
        ->addIndex(['can_login'])
        ->save();

        // 2. 扩展oa_department表
        $table = $this->table('oa_department');
        $table->addColumn('type', 'boolean', [
            'default' => 1,
            'comment' => '部门类型:1内部部门,2外联律所'
        ])
        ->addColumn('firm_id', 'integer', [
            'limit' => 11,
            'default' => 0,
            'comment' => '关联律所ID'
        ])
        ->addColumn('external_code', 'string', [
            'limit' => 100,
            'default' => '',
            'comment' => '外部机构代码'
        ])
        ->addIndex(['type'])
        ->addIndex(['firm_id'])
        ->save();

        // 3. 扩展oa_admin表
        $table = $this->table('oa_admin');
        $table->addColumn('user_type', 'boolean', [
            'default' => 1,
            'comment' => '用户类型:1内部员工,2外联律师'
        ])
        ->addColumn('lawyer_id', 'integer', [
            'limit' => 11,
            'default' => 0,
            'comment' => '关联律师ID'
        ])
        ->addColumn('external_firm', 'string', [
            'limit' => 255,
            'default' => '',
            'comment' => '所属律所名称'
        ])
        ->addIndex(['user_type'])
        ->addIndex(['lawyer_id'])
        ->save();

        // 4. 创建律所部门关联数据
        $this->createLawFirmDepartments();
    }

    public function down()
    {
        // 1. 回滚oa_lawyer表
        $table = $this->table('oa_lawyer');
        $table->removeColumn('admin_id')
              ->removeColumn('can_login')
              ->removeColumn('sync_status')
              ->save();

        // 2. 回滚oa_department表
        $table = $this->table('oa_department');
        $table->removeColumn('type')
              ->removeColumn('firm_id')
              ->removeColumn('external_code')
              ->save();

        // 3. 回滚oa_admin表
        $table = $this->table('oa_admin');
        $table->removeColumn('user_type')
              ->removeColumn('lawyer_id')
              ->removeColumn('external_firm')
              ->save();
    }

    /**
     * 创建律所对应的部门数据
     */
    private function createLawFirmDepartments()
    {
        $lawyers = $this->fetchAll('SELECT DISTINCT firmid, firm_name FROM oa_lawyer WHERE firmid > 0 AND firm_name != ""');
        
        foreach ($lawyers as $lawyer) {
            $exists = $this->fetchRow('SELECT id FROM oa_department WHERE firm_id = ' . $lawyer['firmid']);
            
            if (!$exists) {
                $this->execute('INSERT INTO oa_department (title, type, firm_id, remark, status, create_time, update_time) 
                               VALUES ("' . $lawyer['firm_name'] . '", 2, ' . $lawyer['firmid'] . ', "外联律所", 1, ' . time() . ', ' . time() . ')');
            }
        }
    }

    public function fetchAll(string $sql): array
    {
        return $this->getAdapter()->fetchAll($sql);
    }

    public function fetchRow($sql)
    {
        return $this->getAdapter()->fetchRow($sql);
    }

    public function execute(string $sql, array $params = []): int
    {
        return $this->getAdapter()->execute($sql, $params);
    }
}